set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 20;
set hive.exec.max.dynamic.partitions.pernode = 20;
-- -------------------------------------------------
--    Project Name:  场地数字化网点实际货量统计-汇总数据
--    File Name:     jms_dm.dm_site_digital_real_sum_dt
--    Job Name:      jms_dm__dm_site_digital_real_sum_dt
--    Description:   根据网点到发明细按网点和时间维度汇总
--    Author:        leichao
--    date:          2021-12-11 22:30
-- -------------------------------------------------
with t_base as (
select
    todeliver_date,
    regional_id,
    provider_id,
    agent_code,
    franchisee_code,
    network_code,
    flight_type,
    billcode,
    substring(todeliver_time,12,2) as todeliver_time,
    departure_type,
    regional_desc,
    provider_desc,
    agent_name,
    franchisee_name,
    network_type,
    network_name,
    shipment_no,
    last_next_station_code,
    last_next_station_name,
    last_next_station_type
from jms_dm.dm_site_digital_real_detail_dt
where dt between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
),t_line as (
select
    todeliver_date,network_code,todeliver_time
    ,sum(if(flight_type = '1',1,0)) as deliver_line
    ,sum(if(flight_type = '2',1,0)) as arrival_line
from (
select
    network_code,shipment_no,flight_type,to_date(min(todeliver_time)) as todeliver_date, substring(min(todeliver_time),12,2) as todeliver_time
from jms_dm.dm_site_digital_real_detail_dt
where ( dt between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}' ) and shipment_no is not null and shipment_no != '' and network_type is not null
group by network_code,shipment_no,flight_type
) t1
group by todeliver_date,network_code,todeliver_time
)

insert overwrite table jms_dm.dm_site_digital_real_sum_dt partition(dt)
select
    t1.*,
    nvl(t_line.deliver_line,0) as deliver_line,
    nvl(t_line.arrival_line,0) as arrival_line,
    t1.todeliver_date as dt
from
(
select
    todeliver_date,
    todeliver_time,
    network_code,
    max(regional_id) as regional_id,
    max(regional_desc) as regional_desc,
    max(provider_id) as provider_id,
    max(provider_desc) as provider_desc,
    max(agent_code) as agent_code,
    max(agent_name) as agent_name,
    max(franchisee_code) as franchisee_code,
    max(franchisee_name) as franchisee_name,
    max(network_type) as network_type,
    max(network_name) as network_name,
    sum(if(flight_type = '1' and departure_type = '1',1,0)) as deliver_import,
    sum(if(flight_type = '1' and departure_type = '2',1,0)) as deliver_export,
    sum(if(flight_type = '1' and departure_type = '3',1,0)) as deliver_transfer,
    sum(if(flight_type = '1' ,1,0)) as deliver_sum,
    sum(if(flight_type = '2' and departure_type = '1',1,0)) as arrival_import,
    sum(if(flight_type = '2' and departure_type = '2',1,0)) as arrival_export,
    sum(if(flight_type = '2' and departure_type = '3',1,0)) as arrival_transfer,
    sum(if(flight_type = '2' ,1,0)) as arrival_sum
from t_base
group by todeliver_date,network_code,todeliver_time
) t1 left join t_line
on t1.network_code = t_line.network_code and t1.todeliver_date = t_line.todeliver_date and t1.todeliver_time = t_line.todeliver_time
distribute by 1;